MySQL 批量插入并返回批量自增ID的方法

#MySQL 批量插入并返回批量自增ID的方法| 来源: 网络整理| 查看: 265

在MySQL数据库表中插入大量记录时，若逐条插入时，性能非常性，也比较耗，因此MySQL提供批量插入记录的方法,具体语法为：

Insert into myTable(Field1,Field2,...Fieldn) values (V11,V12,…V1n), (V21,V22,…V2n), ……， (Vn1,Vn2,…Vnn)

上述语句，可以一次性插入大量的数据，具体记录的数量只受系统变量max_allowed_packet的限制，同时提高高数据插入的速度；但有一个问题，就是插入数据后，无法得知插入记录的自增ID，如果需要逐条去查询，那个效率会低得让人无法忍受！

这里就有一种解决办法，可以获取新插入记录的ID，具体思路是：

锁表，并获取当前表的自增ID；执行插入操作；获取当前表的自增ID；表解锁

其中步骤2，3可以放在存储过程中执行(MySQL不支持在存储过程中锁表)；这样的话,前后两次获得的自增ID区间内的值，就是新插入记录的自增ID，可以依据插入顺序为其赋值，

测试表如下：

CREATE TABLE `device` ( `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT, `Name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `IP` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL, `Port` int(8) NULL DEFAULT NULL, PRIMARY KEY (`ID`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Compact;

对测试表作批量插入并取得自增ID的语句如下：

LOCK tables `device` WRITE; -- 锁表 -- 取得插入前的自增ID SELECT `AUTO_INCREMENT` into @MaxID1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'MyDataBase' AND TABLE_NAME = 'device'; INSERT INTO `device`(`ID`, `Name`, `IP`, `Port`) VALUES (0, 'Dev1', '192.168.2.1', 80), (0, 'Dev2', '192.168.2.2', 80), (0, 'Dev3', '192.168.2.3', 80), (0, 'Dev4', '192.168.2.4', 80), (0, 'Dev5', '192.168.2.5', 80); -- 取得插入后的自增ID SELECT `AUTO_INCREMENT` into @MaxID2 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'MyDataBase' AND TABLE_NAME = 'device'; SELECT @MaxID1,@MaxID2; -- 输出自增ID UNLOCK table; -- 解除所有表的锁定

在输入记录中，@MaxID1和@MaxID2区间的ID，就是新增记录的ID，而且是按顺序连续递增的，可以依次分配给插入前的数据；

注意：在实际应用过程中，以上语句可能会存在锁表的隐患，因为插入数据过程中可能会发生异常，导致无法正常执行Unlock talbe，因此建议把步骤2，3放到存储过程中，将在存储过程作异常捕捉，以防止异常影响到Unlock talbe的执行；

【本文地址】

公司简介

联系我们

今日新闻

推荐新闻

专题文章